﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace Automator.Runtime.Classes
{
    /// <summary>
    /// Static class for data-management routines
    /// </summary>
    public static class DataAccess
    {
        public static DataView Select(string DocumentType)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = Automator.Runtime.Settings.SqlConnectionString.ConnectionString;
                conn.Open();
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = conn;
                    command.CommandText = "select d.*, 1 as Visible from Documents d where d.DocumentType=@dtname";
                    command.Parameters.AddWithValue("@dtname", DocumentType);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(dt);
                }
                conn.Close();
            }
            return dt.DefaultView;
        }
        
        public static DataTable Select(Guid Id)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = Automator.Runtime.Settings.SqlConnectionString.ConnectionString;
                conn.Open();
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = conn;
                    command.CommandText = "select d.* from Documents d where d.Id=@id";
                    command.Parameters.AddWithValue("@id", Id);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(dt);
                }
                conn.Close();
            }
            return dt;
        }
        
        public static DataView GetErrorView(string Message)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Name");
            dt.Columns.Add("Visible");
            dt.Rows.Add(Message, 0);
            return dt.DefaultView;
        }
        
        public static DataView GetEmptyView()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Name");
            dt.Columns.Add("Visible");
            dt.Rows.Add("No documents found", 0);
            return dt.DefaultView;
        }
        
        public static void Delete(Guid Id)
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = Automator.Runtime.Settings.SqlConnectionString.ConnectionString;
                conn.Open();
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = conn;
                    command.CommandText = "delete from Documents where Id=@id";
                    command.Parameters.AddWithValue("@id", Id);
                    command.ExecuteNonQuery();
                }
                conn.Close();
            }
        }
        
        public static void Insert(Guid Id, string Name, string Xml, string DocumentType)
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = Automator.Runtime.Settings.SqlConnectionString.ConnectionString;
                conn.Open();
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = conn;
                    command.CommandText = "insert into Documents (Id, Name, XmlData, DocumentType) values (@id, @name, @xml, @doctype)";
                    command.Parameters.AddWithValue("@id", Id);
                    command.Parameters.AddWithValue("@name", Name);
                    command.Parameters.AddWithValue("@xml", Xml);
                    command.Parameters.AddWithValue("@doctype", DocumentType);
                    command.ExecuteNonQuery();
                }
                conn.Close();
            }
        }
        
        public static bool CheckExists(Guid Id)
        {
            object exists = null;
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = Automator.Runtime.Settings.SqlConnectionString.ConnectionString;
                conn.Open();
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = conn;
                    command.CommandText = "select 1 from Documents where Id=@id";
                    command.Parameters.AddWithValue("@id", Id);
                    exists = command.ExecuteScalar();
                }
                conn.Close();
            }
            return (exists != null);
        }
        
        public static void Update(Guid Id, string Xml)
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = Automator.Runtime.Settings.SqlConnectionString.ConnectionString;
                conn.Open();
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = conn;
                    command.CommandText = "update Documents set XmlData=@xml where Id=@id";
                    command.Parameters.AddWithValue("@id", Id);
                    command.Parameters.AddWithValue("@xml", Xml);
                    command.ExecuteNonQuery();
                }
                conn.Close();
            }
        }
    }
}
